package com.atguigu.userprofile.util;

import com.atguigu.userprofile.bean.TagInfo;
import com.atguigu.userprofile.constant.ConstCode;
import com.google.common.base.CaseFormat;
import org.apache.commons.beanutils.BeanUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 负责完成与Mysql的数据操作， 主要实现增删改查.
 */
public class MysqlUtil {

    //private static String url = "jdbc:mysql://hadoop102:3306/user_profile_manager0706" ;
    //private static String url = MyPropsUtil.get("mysql.url");


    private static String url = MyPropsUtil.get(ConstCode.MYSQL_URL);
    private static String username = MyPropsUtil.get(ConstCode.MYSQL_USERNAME);
    private static String password = MyPropsUtil.get(ConstCode.MYSQL_PASSWORD) ;

    /**
     * 通用查询方法  TagInfo , TaskInfo , TaskTagRule ....
     *
     * 返回值:  泛型 、 Object
     *
     * 参数:   String querySql ,  returnType
     */
    public static <T> T  queryOne( String querySql ,  Class<T> returnType , Boolean isUnderScoreToCamel  ){
        List<T> resultList = queryList(querySql, returnType, isUnderScoreToCamel);
        if(!resultList.isEmpty()){
            return resultList.get(0);
        }
        return null ;
    }

    /**
     * JDBC步骤:  注册驱动 获取连接 编写SQL 预编译SQL  设置SQL参数 执行SQL  封装结果 关闭连接
     */
    public static <T> List<T> queryList(String querySql , Class<T> returnType  , Boolean isUnderScoreToCamel){
        ArrayList<T> resultList = new ArrayList<>();
        Connection connection = null ;
        PreparedStatement preparedStatement = null ;
        ResultSet resultSet = null ;

        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获取连接
            connection = DriverManager.getConnection(url, username, password) ;
            //3. 编写SQL
            // querySql
            //4. 预编译SQL
            preparedStatement = connection.prepareStatement(querySql);
            //5. 设置参数
            //6. 执行SQL
            resultSet = preparedStatement.executeQuery();

            //7. 封装结果
            //7.1 获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //每条数据的列的个数
            int columnCount = metaData.getColumnCount();
            while(resultSet.next()){
                // 将查询到的每条数据封装成一个T对象
                T obj = returnType.newInstance(); // 要求T类中有无参数构造器
                for (int i = 1 ; i <= columnCount; i++) {
                    //获取每个列的列名
                    //例如:  tag_name
                    String columnLabel = metaData.getColumnLabel(i);
                    //获取列的值
                    //例如: 人口属性
                    Object columnValue = resultSet.getObject(columnLabel);

                    //将列的值赋值到对象的对应的属性上
                    //例如: new TagInfo().setTagName(columnValue)
                    //将列的名字转换成成属性名 tag_name => tagName
                    String fieldName = null ;
                    if(isUnderScoreToCamel){
                        fieldName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnLabel);
                    }else{
                        fieldName = columnLabel ;
                    }
                    // T obj
                    BeanUtils.copyProperty(obj, fieldName , columnValue);
                }

                //将处理好的每个对象封装到集合中
                resultList.add(obj);
            }

            return resultList ;

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询Mysql失败: " + e.getMessage());
        }finally {
            //8.关闭资源
            try {
                if(resultSet != null && !resultSet.isClosed()){
                    resultSet.close();
                }
                if(preparedStatement != null && !preparedStatement.isClosed()){
                    preparedStatement.close();
                }
                if(connection != null  && !connection.isClosed()){
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("关闭Mysql资源失败: " + e.getMessage());
            }

        }
    }

    public static void main(String[] args) {
        String querySql = "select id , tag_code , tag_name , tag_level , parent_tag_id , tag_type , " +
                " tag_value_type , tag_value_limit , tag_value_step , tag_task_id , tag_comment , " +
                " update_time , create_time from tag_info  where id = 7" ;
        //List<TagInfo> tagInfos = queryList(querySql, TagInfo.class, true);
        //System.out.println(tagInfos.size());
        //tagInfos.forEach(System.out::println);

        TagInfo tagInfo = queryOne(querySql, TagInfo.class, true);
        System.out.println(tagInfo);
    }
}




